﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Configuration;

namespace EnglishRemix.Services
{
    public class EnglishRemixFacade
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string sqlCommand = "";

        public EnglishRemixFacade()
        {
        }

        public string setUserFacade()
        {
            //string currentTime = DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss.fff");
            string currentTime = "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            string sqlstr = "INSERT INTO USERINFO (usr_name, usr_status, usr_level, code, regtime, lastlogin) VALUES ('xxx', 0, 0, '', " + currentTime + "," + currentTime + ");";
            sqlCommand = sqlstr;
            try
            {
                if (SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sqlstr) == 1)
                {
                    return "Database insert successful.";
                }else{
                    return "Database insert failed.";
                }
            }
            catch (Exception e)
            {
                return "An error happened when try to insert into database";
                throw (e);
            }
        }

        public string testFacade()
        {
            string sqlstr = "SELECT * FROM USERINFO";
            string result = "";
            try
            {
                DataSet ds = SqlHelper.ExecuteDataset(connStr, CommandType.Text, sqlstr);
                if (ds != null && ds.Tables.Count > 0)
                {
                    foreach (DataRow eachRow in ds.Tables[0].Rows)
                    {
                        //string name = eachRow["usr_id"].ToString();
                        result += eachRow["regtime"] + " ";
                        //Console.WriteLine(name);
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                return "error";
                throw (e);
            }
        }

        public SampleItem QueryByID(string id)
        {
            string sqlstr = "SELECT *  FROM [SalesLT].[ProductCategory] WHERE id='" + FormatInputData(id) + "'";
            try
            {
                DataSet ds = SqlHelper.ExecuteDataset(connStr, CommandType.Text, sqlstr);

                if (ds != null && ds.Tables.Count > 0)
                {
                    foreach (DataRow eachRow in ds.Tables[0].Rows)
                    {
                        string name = eachRow["Name"].ToString();
                        //eachRow[0].ToString()
                        Console.WriteLine(name);
                    }
                }
            }
            catch (Exception e)
            {
                throw (e);
            }

            throw new NotImplementedException();
        }

        //Todo: we may need a request class for parameter
        public SampleItem[] QueryList(string request)//
        {
            throw new NotImplementedException();
        }

        public string Update(string id, SampleItem obj)
        {
            throw new NotImplementedException();
        }

        public string Insert(SampleItem obj)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            throw new NotImplementedException();
        }


        private string FormatInputData(string id)
        {
            return id.Replace("'", "''");
        }
    }
}